# -*- coding:utf-8 -*-
import sqlite3


# 打开用户数据库
def user_opendb():
    conn = sqlite3.connect("persons.db")
    cur = conn.execute(
        """create table if not exists renyuan (username varchar(128),phone_number varchar(12),password varchar(12))""")
    return cur, conn


# 查询用户全部信息
def user_SelectTable():
    hel = user_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from renyuan")
    res = cur.fetchall()
    # for line in res:
    # for h in line:
    # print(h),
    # print(line)
    cur.close()
    return res

#  往用户数据库中添加内容
def user_InsertData(username, phone_number, password):
    username = str(username)
    phone_number = str(phone_number)
    password = str(password)
    hel = user_opendb()
    hel[1].execute("insert into renyuan(username, phone_number, password)values (?,?,?)",
                   (username, phone_number, password))
    hel[1].commit()
    hel[1].close()

# 查询用户个人信息
def user_showdb(phone_number,password):
    phone_number = str(phone_number)
    hel = user_opendb()
    cur = hel[1].cursor()
    cur.execute("select * from renyuan where phone_number='%s' and password = '%s' " %(phone_number,password))
    res = cur.fetchone()
    return res
    cur.close()


#   删除用户数据库中的全部内容
def user_delalldb():
    hel = user_opendb()  # 返回游标conn
    hel[1].execute("delete from renyuan")
    print("ok")
    hel[1].commit()
    hel[1].close()


#   删除用户数据库中的指定内容
def user_deldb(phone_number):
    try:
        phone_number = str(phone_number)
        hel = user_opendb()  # 返回游标conn
        hel[1].execute("delete from renyuan where phone_number=" + phone_number)
        # print("ok")
        hel[1].commit()
        hel[1].close()
        return 1
    except:
        return 0

#  修改用户数据库的内容
def user_alter(username,phone_number):
    try:
        hel = user_opendb()
        hel[1].execute("update renyuan set username='%s' where phone_number='%s'"%(username,phone_number))
        hel[1].commit()
        hel[1].close()
        return 1
    except:
        return 0